﻿'Description:对结转总表操作类接口
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2018/6/11
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports System.Text
Imports BusinessRule.Common
Imports System.Data.OracleClient
Imports System.Configuration
Imports BusinessRule.DTO

Public Class uLMSContainercurrentinfoBusiness
    Implements IBusiness.uLMSContainercurrentinfoIBusiness

    ''' <summary>
    ''' 在更新工序工区和班组后，更新在制批次的工区班组信息，包括班组派工信息，总表当前班组字段，工区和班组字段
    ''' add:Wangjh 20230424
    ''' </summary>
    ''' <param name="workflowId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateProcessingContainerByWorkflow(ByVal workflowId As String, Optional ByVal stepIds As String = "") As Boolean Implements IBusiness.uLMSContainercurrentinfoIBusiness.UpdateProcessingContainerByWorkflow
        Dim sqlList As New ArrayList
        UpdateProcessingContainerByWorkflow = False
        '查询工序工区班组信息
        Dim strSql = String.Format(<string>select ws.workflowid,ws.workflowstepid,ws.workcenterid,ws.teamid,t.teamname,ws.sequence from workflowstep ws
left join team t on t.teamid=ws.teamid
where ws.workflowid='{0}'</string>.Value, workflowId)
        If stepIds <> "" Then
            strSql += String.Format(" and ws.workflowstepid in ({0}) ", stepIds)
        End If
        Dim wfDt = DatabaseHelper.Query_ReturnDatTable(strSql)
        '查询需要变更的批次
        Dim conDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>select cci.workflowstepsequence,cci.workflowstepid,cci.containerid,cci.stepteamids,cci.stepworkcenterids,cci.teamid,cci.teamname,cci.isdispatch from containercurrentinfo cci 
where cci.workflowid='{0}' and cci.isinstore=0 and cci.factoryname！='装配试车厂' </string>.Value, workflowId))

        For Each conRow As DataRow In conDt.Rows
            '查询此批次的工序班组派工信息
            Dim conTeamDispatchDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>select ctd.containerteamdispatchid,ctd.containerid,ctd.teamid,t.teamname,ctd.workcenterid,ws.workflowstepid,ws.sequence,
nvl2(csd.containerstepdispatchid,1,0) isdispatch from containerteamdispatch ctd
left join team t on t.teamid=ctd.teamid
left join workflowstep ws on ws.workflowstepid=ctd.workflowstepid 
left join containerstepdispatch csd on csd.containerid=ctd.containerid and csd.workflowstepid=ctd.workflowstepid and csd.isuse=0
where ctd.containerid='{0}' and ws.workflowid='{1}' and ctd.isuse=0 </string>.Value, conRow("containerid").ToString, workflowId))
            '查询批次级的班组派工信息
            Dim onlyConTDDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>select ctd.containerteamdispatchid,ctd.containerid,ctd.teamid,t.teamname,ctd.workcenterid,
nvl2(csd.containerstepdispatchid,1,0) isdispatch from containerteamdispatch ctd
left join team t on t.teamid=ctd.teamid 
left join containerstepdispatch csd on csd.containerid=ctd.containerid and csd.workflowstepid=ctd.workflowstepid and csd.isuse=0
where ctd.containerid='{0}' and ctd.workflowstepid is null and ctd.isuse=0 </string>.Value, conRow("containerid").ToString))
            '循环工序
            Dim stepTeamIds As String = ""
            Dim stepWorkcenterIds As String = ""
            For Each steRow As DataRow In wfDt.Rows
                If Val(steRow("sequence").ToString) >= Val(conRow("workflowstepsequence").ToString) Then '当前序之后的工区和班组ids
                    If Not stepTeamIds.Contains(steRow("teamid").ToString) Then
                        stepTeamIds += steRow("teamid").ToString + ","
                    End If
                    If Not stepWorkcenterIds.Contains(steRow("workcenterid").ToString) Then
                        stepWorkcenterIds += steRow("workcenterid").ToString + ","
                    End If
                End If
                Dim _stepDisRows As DataRow() = conTeamDispatchDt.Select(String.Format("workflowstepid='{0}' and ( workcenterid<>'{1}' or teamid<>'{2}' ) and isdispatch=0",
                                                                                       steRow("workflowstepid").ToString, steRow("workcenterid").ToString, steRow("teamid").ToString))
                If _stepDisRows.Length > 0 Then
                    For Each _stepDisRow As DataRow In _stepDisRows
                        sqlList.Add(String.Format("UPDATE CONTAINERTEAMDISPATCH CTD SET TEAMID='{0}',workcenterid='{1}' where ctd.CONTAINERTEAMDISPATCHID='{2}' ",
                                                  steRow("teamid").ToString, steRow("workcenterid").ToString, _stepDisRow("containerteamdispatchid").ToString))
                    Next
                End If
            Next

            Dim updateFileds As String = "" '更新的字段
            If stepTeamIds <> "" Then
                stepTeamIds = stepTeamIds.Trim(",")
            End If
            If stepTeamIds <> "" Then
                stepWorkcenterIds = stepWorkcenterIds.Trim(",")
            End If

            Dim curStepBase = wfDt.Select("workflowstepid='" + conRow("workflowstepid").ToString + "'") '当前序的基本信息
            If curStepBase.Length > 0 Then '首先更新为基础数据的值
                updateFileds = String.Format(",TEAMID='{0}',TEAMNAME='{1}',Teamdispatchid=''", curStepBase(0)("TEAMID").ToString, curStepBase(0)("TEAMNAME").ToString)
            End If

            Dim curStepTeamDispatch As DataRow() = conTeamDispatchDt.Select("workflowstepid='" + conRow("workflowstepid").ToString + "'") '当前序的班组派工记录
            If curStepTeamDispatch.Length > 0 And stepIds.Contains(conRow("workflowstepid").ToString) Then '有当前序的班组派工记录
                updateFileds = String.Format(",TEAMID='{0}',TEAMNAME='{1}',TeamDispatchNum=1,TeamDispatchID='{2}'",
                                             curStepTeamDispatch(0)("TEAMID").ToString, curStepTeamDispatch(0)("TEAMNAME").ToString, curStepTeamDispatch(0)("containerteamdispatchid").ToString)
                'ElseIf conTeamDispatchDt.Select("sequence>" + conRow("workflowstepsequence").ToString).Length > 0 Then '当前序之后，有非当前序的班组派工记录
                '    updateFileds = ",TEAMID='',TEAMNAME='',TeamDispatchNum=1,TeamDispatchID=''"
                'Else '可能有批次班组派工
                '    If onlyConTDDt.Rows.Count > 0 Then
                '        updateFileds = String.Format(",TEAMID='{0}',TEAMNAME='{1}',TeamDispatchNum=1,TeamDispatchID='{2}'",
                '                                     onlyConTDDt.Rows(0)("TEAMID").ToString, onlyConTDDt.Rows(0)("TEAMNAME").ToString,
                '                                     onlyConTDDt.Rows(0)("containerteamdispatchid").ToString)
                '    End If
            End If

            If updateFileds <> "" Then
                sqlList.Add(String.Concat("update containercurrentinfo cci set ", "StepTeamIDs='" + stepTeamIds + "',StepWorkCenterIDs='" + stepWorkcenterIds + "'",
                                          updateFileds, " where cci.containerid='" + conRow("containerid").ToString + "'"))
            End If
            If sqlList.Count > 0 Then
                DatabaseHelper.ExecuteSqlTran(sqlList)
            End If

        Next
        UpdateProcessingContainerByWorkflow = True
    End Function

    ''' <summary>
    ''' 更新总表批次的stepWorkcenters或stepTeams字段
    ''' </summary>
    ''' <param name="workflowID"></param>
    ''' <param name="strValues"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateWorkcentersOrTeamsByWorkflowID(ByVal workflowID As String, ByVal strValues As String, ByVal type As String) As Boolean Implements IBusiness.uLMSContainercurrentinfoIBusiness.UpdateWorkcentersOrTeamsByWorkflowID
        Dim strSql As String = ""
        If type = "0" Then
            strSql = String.Format("UPDATE containercurrentinfo cci SET cci.stepworkcenterids='{0}' WHERE CCI.Isdispatch=0 AND cci.workflowid='{1}' AND (cci.stepworkcenterids<>'{0}' or cci.stepworkcenterids is null)", strValues, workflowID)
        ElseIf type = "1" Then
            strSql = String.Format("UPDATE containercurrentinfo cci SET cci.stepteamids='{0}' WHERE CCI.Isdispatch=0 AND cci.workflowid='{1}' AND (cci.stepteamids<>'{0}' or cci.stepteamids is null)", strValues, workflowID)
        End If
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql)
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 更新总结转表检验信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowstepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerCurrentinfoCheckInfo(ByVal containerID As String, ByVal workflowstepID As String) As String Implements BusinessRule.IBusiness.uLMSContainercurrentinfoIBusiness.UpdateContainerCurrentinfoCheckInfo
        Dim stepCheckNum As Integer = 0
        Dim firstChekNum As Integer = 0
        Dim strSql As String = <string>SELECT cci.containercheckinfoid,csw.*  FROM containerstepworkreport csw 
        LEFT JOIN containercheckinfo cci ON cci.containerstepworkreportid=csw.containerstepworkreportid AND cci.isuse=0
        WHERE csw.isuse=0</string>
        strSql += String.Format(" AND csw.containerid='{0}' AND csw.workflowstepid='{1}'", containerID, workflowstepID)
        strSql += " AND ((csw.containerfinishedcheckinfoid IS NULL AND cci.containercheckinfoid IS NULL) OR csw.checktype=1)"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        '更新firstChekNum
        Dim drs As DataRow() = dt.Select("checktype=1")
        If drs.Length > 0 Then '有首检的记录，首检只能是一次
            If drs(0).IsNull("containercheckinfoid") Then '首件报工没有检验
                firstChekNum = 1
            End If
            If drs(0).IsNull("lossqty") Then '首检没有报废
                firstChekNum = 2
            End If
        End If
        '
        '更新stepCheckNum
        drs = Nothing
        drs = dt.Select("checktype<>1")
        If drs.Length > 0 Then '有其他检的记录
            stepCheckNum = 1
        End If
        '
        strSql = String.Format("UPDATE containercurrentinfo cci SET cci.firstchecknum={0} ,cci.stepchecknum={1} WHERE cci.containerid='{2}'",
                                firstChekNum, stepCheckNum, containerID)
        DatabaseHelper.ExecuteSql(strSql)

        Return "更新" + strSql

    End Function

    ''' <summary>
    ''' 更新总结转表检验信息（工步）
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowstepID"></param>
    ''' <param name="stepInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerCurrentinfoCheckInfo2(ByVal containerID As String, ByVal workflowstepID As String) As String Implements BusinessRule.IBusiness.uLMSContainercurrentinfoIBusiness.UpdateContainerCurrentinfoCheckInfo2
        Dim stepCheckNum As Integer = 0
        Dim firstChekNum As Integer = 0
        Dim strSql As String = <string>SELECT cci.containercheckinfoid,csw.*  FROM containerstepworkreport2 csw 
        LEFT JOIN containercheckinfo2 cci ON cci.containerstepworkreportid=csw.containerstepworkreportid AND cci.isuse=0
        WHERE csw.isuse=0</string>
        strSql += String.Format(" AND csw.containerid='{0}' AND csw.workflowstepid='{1}'", containerID, workflowstepID)
        strSql += " AND ((csw.containerfinishedcheckinfoid IS NULL AND cci.containercheckinfoid IS NULL) OR csw.checktype=1)"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        '更新firstChekNum
        Dim drs As DataRow() = dt.Select("checktype=1")
        If drs.Length > 0 Then '有首检的记录，首检只能是一次
            If drs(0).IsNull("containercheckinfoid") Then '首件报工没有检验
                firstChekNum = 1
            End If
            If drs(0).IsNull("lossqty") Then '首检没有报废
                firstChekNum = 2
            End If
        End If
        '
        '更新stepCheckNum
        drs = Nothing
        drs = dt.Select("checktype<>1")
        If drs.Length > 0 Then '有其他检的记录
            stepCheckNum = 1
        End If
        '
        strSql = String.Format("UPDATE containercurrentinfo cci SET cci.firstchecknum={0} ,cci.stepchecknum={1} WHERE cci.containerid='{2}'",
                                firstChekNum, stepCheckNum, containerID)
        DatabaseHelper.ExecuteSql(strSql)

        Return "更新" + strSql

    End Function
    ''' <summary>
    ''' 更新总表的某些字段值
    ''' </summary>
    ''' <param name="updatePara"></param>
    ''' <param name="conditionPara"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerCurrentInfoByField(ByVal updatePara As Dictionary(Of String, String), ByVal conditionPara As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSContainercurrentinfoIBusiness.UpdateContainerCurrentInfoByField
        UpdateContainerCurrentInfoByField = False
        If updatePara.Count = 0 And conditionPara.Count = 0 Then
            Exit Function
        End If
        Dim strSql As String = <string>UPDATE containercurrentinfo SET </string>
        Dim n = 0
        For Each updateKey As String In updatePara.Keys
            If n = 0 Then
                strSql += updateKey + "=" + updatePara(updateKey) + ""
            Else
                strSql += "," + updateKey + "=" + updatePara(updateKey) + ""
            End If
            n += 1
        Next
        n = 0
        For Each conditionKey As String In conditionPara.Keys
            If n = 0 Then
                strSql += " where " + conditionKey + "=" + conditionPara(conditionKey) + ""
            Else
                strSql += " and " + conditionKey + "=" + conditionPara(conditionKey) + ""
            End If
            n += 1
        Next
        If Not DatabaseHelper.ExecuteSql(strSql) > 0 Then
            Exit Function
        End If
        UpdateContainerCurrentInfoByField = True
    End Function

    ''' <summary>
    ''' 校验结转总表某些字段是否更新
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckContainerCurrentInfoFiled(ByVal containerID As String, ByVal filed As String, ByVal value As String) As Boolean Implements IBusiness.uLMSContainercurrentinfoIBusiness.CheckContainerCurrentInfoFiled
        CheckContainerCurrentInfoFiled = False
        Dim strSql As String = "SELECT *  FROM  containercurrentinfo cci WHERE cci.containerid='" + containerID + "'"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count = 0 Or Not dt.Columns.Contains(filed) Then
            Exit Function
        End If
        If dt.Rows(0)(filed).ToString <> value Then
            Exit Function
        End If

        CheckContainerCurrentInfoFiled = True
    End Function

#Region "TansferInsiteData服务代码"

#Region "公共方法 creat:Wangjh"
    ''' <summary>
    ''' 更新表的结转标记
    ''' </summary>
    ''' <param name="tableName"></param>
    ''' <param name="ID"></param>
    ''' <param name="IDValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateTransferOfTable(ByVal tableName As String, ByVal ID As String, ByVal IDValue As Object) As Boolean

        Dim updateSql As String = "UPDATE " + tableName + " T SET ISTRANSFER=1 WHERE T." + ID + "=:ID"
        Dim updateParam(0) As OracleParameter
        updateParam(0) = DatabaseHelper.NewParameter(":ID", IDValue, OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(updateSql, updateParam)
        'InsertOracleLog("更新表" + tableName + "结转标记", i, LogHelp.TranferSql(updateSql, updateParam), "")
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 插入日志记录
    ''' </summary>
    ''' <param name="name"></param>
    ''' <param name="message"></param>
    ''' <param name="i"></param>
    ''' <param name="sql"></param>
    ''' <param name="containerName"></param>
    ''' <remarks></remarks>
    Sub InsertOracleLog(ByVal name As String, ByVal message As String, ByVal i As Integer, ByVal sql As String, ByVal containerName As String)
        Dim IsLog = ConfigurationManager.AppSettings("IsOracleLog").ToString()
        If IsLog = "是" Then
            LogHelp.WriteOrcale("TransferInsiteData_" + name + "@^" + message + "i=" + i.ToString + "@^" + sql + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + containerName)
        End If
    End Sub
#End Region

#Region "插入批次基本信息 creat:Wangjh"
    ''' <summary>
    ''' 获取批次信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerBaseInfo(ByVal containerID As String) As DataTable
        Dim strSql As String = <string>SELECT CDO.CDONAME,C.CONTAINERID,C.CONTAINERNAME,C.QTY,C.CONTAINERTYPE,C.STATUS CONTAINERSTATUS,C.ORIGINALFACTORYID,
                                   C.TOGETHERFLAG,C.ISSELFMFG,PB.PRODUCTNAME,P.PRODUCTREVISION,P.DESCRIPTION PRODUCTDESCRIPTION,P.PRODUCTID,PB.PRODUCTBASEID,
                                   NULL bomid,M.MFGORDERNAME,M.QTY PLANQTY,M.MFGORDERID,OT.ORDERTYPENAME MFGORDERTYPE,
                                   M.PLANNEDSTARTDATE,M.PLANNEDCOMPLETIONDATE,FA.FACTORYNAME,FA.FACTORYID,WC.WORKCENTERNAME,WC.WORKCENTERID,
                                   BT.PRODUCTFAMILYNAMES FAMILYLISTNAMES,BT.productfamilyids FAMILYLISTIDS,WF.WORKFLOWID,c.splitfromid,DECODE(cdo.cdoname,'MachineMfgWorkFlow',0,-1) isinstore,cci2.techniquedate,cci2.techniqueemployeeid,
                                   p.workflowroute FROM CONTAINER C
                                   LEFT JOIN PRODUCT P ON C.PRODUCTID = P.PRODUCTID
                                   INNER JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID
                                   LEFT JOIN MFGORDER M  ON M.MFGORDERID = C.MFGORDERID 
                                   LEFT JOIN ORDERTYPE OT ON OT.ORDERTYPEID=M.ORDERTYPEID
                                   INNER JOIN CURRENTSTATUS CU  ON CU.CURRENTSTATUSID = C.CURRENTSTATUSID
                                   LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CU.WORKFLOWSTEPID
                                   LEFT JOIN WORKFLOW WF  ON WF.WORKFLOWID = WS.WORKFLOWID
                                   LEFT JOIN CDODEFINITION CDO ON CDO.CDODEFID = WF.CDOTYPEID
                                   LEFT JOIN WORKCENTER WC  ON WC.WORKCENTERID = C.WORKCENTERID
                                   LEFT JOIN FACTORY FA ON FA.FACTORYID=C.ORIGINALFACTORYID
                                   LEFT JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID=C.CONTAINERID
                                   LEFT JOIN containercurrentinfo cci2 ON cci2.containerid=c.splitfromid--当前为分批卡
                                    LEFT JOIN(
                                          SELECT CFL.CONTAINERID,REPLACE (wm_concat(PF.PRODUCTFAMILYNAME),',',' ') PRODUCTFAMILYNAMES,wm_concat(pf.productfamilyid) productfamilyids FROM CONTAINERPRODUCTFAMILYLIST CFL 
                                                LEFT JOIN PRODUCTFAMILY PF ON PF.PRODUCTFAMILYID=CFL.PRODUCTFAMILYLISTID
                                                GROUP BY CFL.CONTAINERID
                                                ) BT ON BT.CONTAINERID=C.CONTAINERID--查出机型
                                   WHERE C.STATUS =1 AND C.PARENTCONTAINERID IS NULL AND C.TOGETHERFLAG not = ' ' 
                                   AND C.QTY > 0 AND CCI.CONTAINERID IS NULL</string>
        strSql += " AND C.CONTAINERID='" + containerID + "'"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 插入批次基本信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertContainerBaseInfoOfRow(ByVal dr As DataRow) As Boolean
        Dim inserSql As String = <string>INSERT INTO CONTAINERCURRENTINFO C(CONTAINERID,CONTAINERNAME,QTY,CONTAINERTYPE,CONTAINERSTATUS,
        ORIGINALFACTORYID, TOGETHERFLAG, ISSELFMFG, PRODUCTNAME, PRODUCTREVISION, PRODUCTDESCRIPTION, PRODUCTID,
        PRODUCTBASEID, BOMID, MFGORDERNAME, PLANQTY, MFGORDERID, MFGORDERTYPE, PLANNEDSTARTDATE, PLANNEDCOMPLETIONDATE,
        FACTORYNAME, FACTORYID, WORKCENTERNAME, WORKCENTERID, FromWorkCenterName, FromWorkCenterID, ArrangementNum,
        DispatchNum, IsDispatch, DispatchChangeNum, WorkCenterDispatchNum, TeamDispatchNum, FirstCheckNum,
        StepCheckNum, SplitNum, ShipNum, IsShip, ISREWORK, ISINSTORE, ALLLOSSQTY, COMMISSIONINGNUM, ISCOMMISSIONING
                                     ,FAMILYLISTNAMES,FAMILYLISTIDS,splitfromid,techniquedate,techniqueemployeeid,workflowroute)
                                     VALUES(:CONTAINERID,:CONTAINERNAME,:QTY,:CONTAINERTYPE,:CONTAINERSTATUS,:ORIGINALFACTORYID,
                                    :TOGETHERFLAG,:ISSELFMFG,:PRODUCTNAME,:PRODUCTREVISION,:PRODUCTDESCRIPTION,:PRODUCTID,:PRODUCTBASEID,
                                    :BOMID,:MFGORDERNAME,:PLANQTY,:MFGORDERID,:MFGORDERTYPE,:PLANNEDSTARTDATE,:PLANNEDCOMPLETIONDATE,
                                    :FACTORYNAME,:FACTORYID,:WORKCENTERNAME,:WORKCENTERID,:FromWorkCenterName,:FromWorkCenterID,0,0,0,
                                     0,0,0,0,0,0,0,0,0,:ISINSTORE,0,0,0,:FAMILYLISTNAMES,:FAMILYLISTIDS,:splitfromid,:techniquedate,:techniqueemployeeid,:workflowroute)</string>
        Dim insertParam(32) As OracleParameter
        insertParam(0) = DatabaseHelper.NewParameter(":CONTAINERID", dr("CONTAINERID"), OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":CONTAINERNAME", dr("CONTAINERNAME"), OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":QTY", dr("QTY"), OracleType.Number)
        insertParam(3) = DatabaseHelper.NewParameter(":CONTAINERTYPE", dr("CONTAINERTYPE"), OracleType.Number)
        insertParam(4) = DatabaseHelper.NewParameter(":CONTAINERSTATUS", dr("CONTAINERSTATUS"), OracleType.Number)
        insertParam(5) = DatabaseHelper.NewParameter(":ORIGINALFACTORYID", dr("ORIGINALFACTORYID"), OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":TOGETHERFLAG", dr("TOGETHERFLAG"), OracleType.VarChar)
        insertParam(7) = DatabaseHelper.NewParameter(":ISSELFMFG", dr("ISSELFMFG"), OracleType.Number)
        insertParam(8) = DatabaseHelper.NewParameter(":PRODUCTNAME", dr("PRODUCTNAME"), OracleType.VarChar)
        insertParam(9) = DatabaseHelper.NewParameter(":PRODUCTREVISION", dr("PRODUCTREVISION"), OracleType.VarChar)
        insertParam(10) = DatabaseHelper.NewParameter(":PRODUCTDESCRIPTION", dr("PRODUCTDESCRIPTION"), OracleType.VarChar)
        insertParam(11) = DatabaseHelper.NewParameter(":PRODUCTID", dr("PRODUCTID"), OracleType.VarChar)
        insertParam(12) = DatabaseHelper.NewParameter(":PRODUCTBASEID", dr("PRODUCTBASEID"), OracleType.VarChar)
        insertParam(13) = DatabaseHelper.NewParameter(":BOMID", dr("BOMID"), OracleType.VarChar)
        insertParam(14) = DatabaseHelper.NewParameter(":MFGORDERNAME", dr("MFGORDERNAME"), OracleType.VarChar)
        insertParam(15) = DatabaseHelper.NewParameter(":PLANQTY", dr("PLANQTY"), OracleType.Number)
        insertParam(16) = DatabaseHelper.NewParameter(":MFGORDERID", dr("MFGORDERID"), OracleType.VarChar)
        insertParam(17) = DatabaseHelper.NewParameter(":MFGORDERTYPE", dr("MFGORDERTYPE"), OracleType.VarChar)
        insertParam(18) = DatabaseHelper.NewParameter(":PLANNEDSTARTDATE", dr("PLANNEDSTARTDATE"), OracleType.DateTime)
        insertParam(19) = DatabaseHelper.NewParameter(":PLANNEDCOMPLETIONDATE", dr("PLANNEDCOMPLETIONDATE"), OracleType.DateTime)
        insertParam(20) = DatabaseHelper.NewParameter(":FACTORYNAME", dr("FACTORYNAME"), OracleType.VarChar)
        insertParam(21) = DatabaseHelper.NewParameter(":FACTORYID", dr("FACTORYID"), OracleType.VarChar)
        insertParam(22) = DatabaseHelper.NewParameter(":WORKCENTERNAME", dr("WORKCENTERNAME"), OracleType.VarChar)
        insertParam(23) = DatabaseHelper.NewParameter(":WORKCENTERID", dr("WORKCENTERID"), OracleType.VarChar)
        insertParam(24) = DatabaseHelper.NewParameter(":FromWorkCenterName", dr("WORKCENTERNAME"), OracleType.VarChar)
        insertParam(25) = DatabaseHelper.NewParameter(":FromWorkCenterID", dr("WORKCENTERID"), OracleType.VarChar)
        insertParam(26) = DatabaseHelper.NewParameter(":FAMILYLISTNAMES", dr("FAMILYLISTNAMES").ToString().Trim(), OracleType.VarChar)
        insertParam(27) = DatabaseHelper.NewParameter(":FAMILYLISTIDS", dr("FAMILYLISTIDS"), OracleType.VarChar)
        insertParam(28) = DatabaseHelper.NewParameter(":splitfromid", dr("splitfromid"), OracleType.VarChar)
        insertParam(29) = DatabaseHelper.NewParameter(":ISINSTORE", dr("ISINSTORE"), OracleType.Number)
        insertParam(30) = DatabaseHelper.NewParameter(":techniquedate", dr("techniquedate"), OracleType.DateTime)
        insertParam(31) = DatabaseHelper.NewParameter(":techniqueemployeeid", dr("techniqueemployeeid"), OracleType.VarChar)
        insertParam(32) = DatabaseHelper.NewParameter(":workflowroute", dr("workflowroute"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(inserSql, insertParam)
        InsertOracleLog(System.Reflection.MethodBase.GetCurrentMethod().Name, "插入批次基本信息", i, LogHelp.TranferSql(inserSql, insertParam), dr("CONTAINERNAME").ToString())
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 创建批次基本信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CreateContainerBaseInfo(ByVal containerID As String) As Boolean
        CreateContainerBaseInfo = False
        Dim containerData = GetContainerBaseInfo(containerID)
        For Each dr As DataRow In containerData.Rows
            If InsertContainerBaseInfoOfRow(dr) = False Then
                Exit Function
            End If
        Next
        CreateContainerBaseInfo = True
    End Function
#End Region

#Region "更新自动报工或外委数量信息(适用于调度数量变更) create:Wangjh "

    ''' <summary>
    ''' 更新自动转工、检验和外委的数量变化信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <remarks></remarks>
    Sub UpdateAutoInfo(ByVal containerID As String, ByVal qty As Integer, ByVal changeQty As Integer, ByVal stepID As String) Implements IBusiness.uLMSContainercurrentinfoIBusiness.UpdateAutoInfo
        Dim updateSql = String.Format("UPDATE containerstepworkreport csw SET csw.qty={0}," +
                                          " csw.istransfer=1 WHERE csw.containerid='{1}' AND csw.workflowstepid='{2}' AND csw.workerid IS NULL",
                                          qty, containerID, stepID)
        If IsChecked(containerID, stepID) = False Then '非检验变更数量才改
            If DatabaseHelper.ExecuteSql(updateSql) > 0 Then '更新自动报工数量
                '这里可能更新总表总报工数
                updateSql = String.Format(" UPDATE containercurrentinfo cci SET cci.workreportnum=cci.workreportnum+{0} WHERE cci.containerid='{1}' AND cci.workflowstepid='{2}'",
                                              changeQty, containerID, stepID)
                '
                DatabaseHelper.ExecuteSql(updateSql)
            End If

        End If

        updateSql = String.Format("UPDATE containercommissioning  cmm SET cmm.outqty={0} " +
                                "WHERE cmm.containerid='{1}' AND cmm.workflowstepid='{2}' AND cmm.outemployeeid IS NULL",
                                qty, containerID, stepID)
        DatabaseHelper.ExecuteSql(updateSql) '更新自动外委数量

    End Sub

    ''' <summary>
    ''' 是否存在检验
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="stepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsChecked(ByVal containerID As String, ByVal stepID As String) As Boolean
        Dim strSql = String.Format(<string>SELECT cqc.*  FROM checkqtychangerecord cqc
                                LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=cqc.containerstepworkreportid AND csw.isuse=0
                                        WHERE cqc.isuse=0  AND  csw.containerid='{0}' AND csw.workflowstepid='{1}'</string>.Value,
               containerID, stepID)
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

#End Region

#Region "更新班组信息 creat:Wangjh"
    ''' <summary>
    ''' 获取派到班组信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTeamDispachInfo(ByVal containerID As String, ByVal teamDispatchID As String) As DataTable
        Dim strSql As String = <string>
                                 SELECT DISTINCT T.TEAMNAME,CTD.TEAMID,CTD.CONTAINERTEAMDISPATCHID AS TEAMDISPATCHID,CCI.CONTAINERNAME,
                               DECODE(CTD.CONTAINERTEAMDISPATCHID, NULL, 0, 1) TEAMDISPATCHNUM,CTD.CONTAINERID,CTD.ISUSE,
                               CTD.ISTRANSFER,CTD.WORKFLOWSTEPID,CCI.WORKFLOWSTEPID CUWORKFLOWSTEPID,CCI.TEAMID CCITEAMID,CCI.TEAMNAME CCITEAMNAME, 
                               CCI.WORKFLOWSTEPSEQUENCE,CCI.WORKFLOWID,CCI.CONTAINERNAME
                               FROM CONTAINERTEAMDISPATCH CTD 
                               INNER JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID = CTD.CONTAINERID	
                               LEFT JOIN TEAM T ON T.TEAMID=CTD.TEAMID																                              
                               WHERE CTD.ISTRANSFER=0 
                             </string>
        If Not String.IsNullOrWhiteSpace(teamDispatchID) Then
            strSql += " and CTD.CONTAINERTEAMDISPATCHID ='" + teamDispatchID + "'"
        Else
            strSql += " and CCI.CONTAINERID ='" + containerID + "'"
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 更新班组派工信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateTeamDispachOfRow(ByVal dr As DataRow) As Boolean
        Dim updateSql = <string>UPDATE CONTAINERCURRENTINFO C SET TEAMNAME=:TEAMNAME,TEAMID=:TEAMID,TEAMDISPATCHID=:TEAMDISPATCHID,
                                  TEAMDISPATCHNUM=:TEAMDISPATCHNUM
                                  WHERE C.CONTAINERID=:CONTAINERID</string>
        Dim updateParam(4) As OracleParameter
        updateParam(0) = DatabaseHelper.NewParameter(":TEAMNAME", dr("TEAMNAME"), OracleType.VarChar)
        updateParam(1) = DatabaseHelper.NewParameter(":TEAMID", dr("TEAMID"), OracleType.VarChar)
        updateParam(2) = DatabaseHelper.NewParameter(":TEAMDISPATCHID", dr("TEAMDISPATCHID"), OracleType.VarChar)
        updateParam(3) = DatabaseHelper.NewParameter(":CONTAINERID", dr("CONTAINERID"), OracleType.VarChar)

        updateParam(4) = New OracleParameter(":TEAMDISPATCHNUM", OracleType.Number)
        updateParam(4).Direction = ParameterDirection.Input
        updateParam(4).Value = dr("TEAMDISPATCHNUM")
        Dim i = DatabaseHelper.ExecuteSql(updateSql, updateParam)
        InsertOracleLog(System.Reflection.MethodBase.GetCurrentMethod().Name, "更新班组信息", i, LogHelp.TranferSql(updateSql, updateParam), dr("CONTAINERNAME").ToString())

        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 循环更新班组派工信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <remarks></remarks>
    Public Sub UpdateTeamDispach(ByVal containerID As String, ByVal teamDispatchID As String) Implements IBusiness.uLMSContainercurrentinfoIBusiness.UpdateTeamDispach
        Dim containerData = GetTeamDispachInfo(containerID, teamDispatchID)
        Dim tempDv = containerData.DefaultView
        tempDv.RowFilter = " WORKFLOWSTEPID=CUWORKFLOWSTEPID AND ISUSE=0" '筛选当前工序的班组派工
        Dim tempDt = tempDv.ToTable()

        Dim tempDv2 = containerData.DefaultView
        tempDv2.RowFilter = " WORKFLOWSTEPID IS NOT NULL AND  WORKFLOWSTEPID<>CUWORKFLOWSTEPID AND ISUSE=0" '筛选不是当前工序的班组派工
        Dim tempDt2 = tempDv2.ToTable()

        Dim tempDv3 = containerData.DefaultView
        tempDv3.RowFilter = " WORKFLOWSTEPID IS  NULL AND ISUSE=0 " '筛选批次班组派工
        Dim tempDt3 = tempDv3.ToTable()

        Dim tempDv4 = containerData.DefaultView
        tempDv4.RowFilter = "  ISUSE=1 " '筛选移除的班组派工
        Dim tempDt4 = tempDv4.ToTable()

        Dim tempDv5 = containerData.DefaultView
        tempDv5.RowFilter = ""
        Dim tempDt5 = tempDv5.ToTable(True, "CONTAINERID", "WORKFLOWID", "WORKFLOWSTEPSEQUENCE")

        If tempDt4.Rows.Count > 0 Then '更新移除班组派工信息
            For Each dr As DataRow In tempDt4.Rows
                UpdateTransferOfTable("CONTAINERTEAMDISPATCH", "CONTAINERTEAMDISPATCHID", dr("TEAMDISPATCHID"))
                dr("TEAMNAME") = ""
                dr("TEAMID") = ""
                dr("TEAMDISPATCHID") = ""
                dr("TEAMDISPATCHNUM") = 0
                UpdateTeamDispachOfRow(dr)
            Next
        End If

        If tempDt3.Rows.Count > 0 Then '更新批次班组派工信息
            For Each dr As DataRow In tempDt3.Rows
                UpdateTeamDispachOfRow(dr)
                UpdateTransferOfTable("CONTAINERTEAMDISPATCH", "CONTAINERTEAMDISPATCHID", dr("TEAMDISPATCHID").ToString)
            Next
        End If

        If tempDt2.Rows.Count > 0 Then '更新不是当前工序班组派工信息
            For Each dr As DataRow In tempDt2.Rows
                UpdateTransferOfTable("CONTAINERTEAMDISPATCH", "CONTAINERTEAMDISPATCHID", dr("TEAMDISPATCHID").ToString)
                dr("TEAMNAME") = dr("CCITEAMNAME").ToString
                dr("TEAMID") = dr("CCITEAMID").ToString
                dr("TEAMDISPATCHID") = ""
                dr("TEAMDISPATCHNUM") = 1
                UpdateTeamDispachOfRow(dr)
            Next
        End If

        If tempDt.Rows.Count > 0 Then '更新当前工序班组派工信息
            For Each dr As DataRow In tempDt.Rows
                UpdateTeamDispachOfRow(dr)
                UpdateTransferOfTable("CONTAINERTEAMDISPATCH", "CONTAINERTEAMDISPATCHID", dr("TEAMDISPATCHID").ToString)
            Next
        End If

        If tempDt5.Rows.Count > 0 Then '更新总表班组IDs信息
            For Each dr As DataRow In tempDt5.Rows
                UpdateStepTeamIDOfRow(dr)
            Next
        End If
    End Sub
#End Region

#Region "更新批次当前序后所有序的班组ID信息 create wangjh 2017/10/30"
    ''' <summary>
    ''' 获取批次当前工序后所有班组信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTeamIDs(ByVal dr As DataRow) As DataTable
        Dim strSql As String = <string>SELECT  WM_CONCAT(TEAMID) TEAMIDS FROM 
                            (SELECT DISTINCT CTD.TEAMID FROM CONTAINERTEAMDISPATCH CTD
                            LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CTD.WORKFLOWSTEPID</string>
        strSql += String.Format(" WHERE WS.WORKFLOWID = '{0}' AND WS.SEQUENCE >= {1} AND CTD.CONTAINERID='{2}' AND CTD.ISUSE=0)", dr("WORKFLOWID"), dr("WORKFLOWSTEPSEQUENCE"), dr("CONTAINERID"))

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 更新批次当前工序后所有班组信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateStepTeamIDOfRow(ByVal dr As DataRow) As Boolean
        Dim teamIDs = GetTeamIDs(dr)
        Dim strTeamIDs = teamIDs.Rows(0)("TEAMIDS").ToString()
        '将获取的WorkCenterIDs更新到汇总表上
        Dim strsql = String.Format(" UPDATE CONTAINERCURRENTINFO CCI SET CCI.STEPTEAMIDS='{0}' WHERE CCI.CONTAINERID='{1}'", strTeamIDs, dr("CONTAINERID"))
        Dim i = DatabaseHelper.ExecuteSql(strsql)
        InsertOracleLog(System.Reflection.MethodBase.GetCurrentMethod().Name, "更新STEPTEAMIDS", i, strsql, dr("CONTAINERID").ToString()) 'add:Wangjh 20180531 日志记录

        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
#End Region

#Region "工区协作信息 creat:Wangjh"
    ''' <summary>
    ''' 获取工区协作数据
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkCenterDispachInfo(ByVal containerID As String) As DataTable
        Dim strSql = <string>SELECT W.WORKCENTERNAME,CWD.WORKCENTERID,(CASE WHEN cwd.containerworkcenterdispatchid IS NOT NULL AND cwd.recvemployeeid IS NULL THEN 1 ELSE
                            0 END) WORKCENTERDISPATCHNUM,
                            CWD.CONTAINERWORKCENTERDISPATCHID WORKCENTERDISPATCHID,CWD.TOWORKCENTERID,C.CONTAINERID,CWD.ISUSE,CWD.ISTRANSFER,  
                            CWD.WORKFLOWSTEPID,C.WORKFLOWSTEPID AS CUWORKFLOWSTEPID,C.Workflowid cuWorkflowid,WS.SEQUENCE,CWD.RECVEMPLOYEEID,C.CONTAINERNAME,C.workflowstepsequence cusequence FROM CONTAINERWORKCENTERDISPATCH CWD--工区协作
                            INNER JOIN CONTAINERCURRENTINFO C ON CWD.CONTAINERID = C.CONTAINERID
                            LEFT JOIN WORKCENTER W ON W.WORKCENTERID=CWD.WORKCENTERID
                            LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CWD.WORKFLOWSTEPID
                            WHERE ws.SEQUENCE>=c.workflowstepsequence </string>.Value

        strSql += " and c.CONTAINERID ='" + containerID + "' ORDER BY cwd.createdate,WS.SEQUENCE"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 更新工区协作数据
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWorkCenterDispachOfRow(ByVal dr As DataRow) As Boolean
        Dim updateSql = <string>UPDATE CONTAINERCURRENTINFO C SET WORKCENTERNAME=:WORKCENTERNAME,WORKCENTERID=:WORKCENTERID,
                                 WORKCENTERDISPATCHNUM=:WORKCENTERDISPATCHNUM,WORKCENTERDISPATCHID=:WORKCENTERDISPATCHID,
                                 TOWORKCENTERID=:TOWORKCENTERID
                                 WHERE C.CONTAINERID=:CONTAINERID</string>.Value
        Dim updateParam(5) As OracleParameter
        updateParam(0) = DatabaseHelper.NewParameter(":WORKCENTERNAME", dr("WORKCENTERNAME"), OracleType.VarChar)
        updateParam(1) = DatabaseHelper.NewParameter(":WORKCENTERID", dr("WORKCENTERID"), OracleType.VarChar)
        updateParam(2) = DatabaseHelper.NewParameter(":WORKCENTERDISPATCHID", dr("WORKCENTERDISPATCHID"), OracleType.VarChar)
        updateParam(3) = DatabaseHelper.NewParameter(":CONTAINERID", dr("CONTAINERID"), OracleType.VarChar)
        updateParam(4) = DatabaseHelper.NewParameter(":TOWORKCENTERID", dr("TOWORKCENTERID"), OracleType.VarChar)
        updateParam(5) = New OracleParameter(":WORKCENTERDISPATCHNUM", OracleType.Number)
        updateParam(5).Direction = ParameterDirection.Input
        updateParam(5).Value = dr("WORKCENTERDISPATCHNUM")
        Dim i = DatabaseHelper.ExecuteSql(updateSql, updateParam)
        InsertOracleLog(System.Reflection.MethodBase.GetCurrentMethod().Name, "更新工区协作信息", i, LogHelp.TranferSql(updateSql, updateParam), dr("CONTAINERNAME").ToString())

        If i > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    ''' <summary>
    ''' 判断指定流水卡除指定工序外是否还有协作的工序,0:无协作的 1:协作未接收 2:全是协作已接收 3:部分协作已接收,还有协作未接收
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <param name="toWorkcenterID"></param>
    ''' <param name="workcenterdispatchID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsDispachStep(ByVal dr As DataRow, ByRef toWorkcenterID As String, ByRef workcenterdispatchID As String) As Integer
        Dim strSql = <string>SELECT cwd.recvdate,cwd.workflowstepid,cwd.toworkcenterid,cwd.containerworkcenterdispatchid FROM containerworkcenterdispatch CWD
                              LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CWD.WORKFLOWSTEPID
                              WHERE CWD.CONTAINERWORKCENTERDISPATCHID IS NOT NULL AND CWD.ISUSE=0</string>.Value
        strSql += String.Format(" AND CWD.WORKCENTERID='{0}' AND CWD.CONTAINERID='{1}' AND WS.SEQUENCE<>{2} and ws.SEQUENCE>={3} and ws.workflowid='{4}'",
               dr("WORKCENTERID"), dr("CONTAINERID"), dr("SEQUENCE"), dr("cusequence"), dr("cuWorkflowid"))
        strSql += " order by WS.SEQUENCE asc"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim drs = dt.Select("recvdate is not null")
        If dt.Select("recvdate is not null and workflowstepid='" + dr("CUWORKFLOWSTEPID").ToString() + "'").Length > 0 Then
            workcenterdispatchID = drs(0)("containerworkcenterdispatchid").ToString()
        End If
        If dt.Rows.Count = 0 Then
            Return 0
        Else
            If dt.Select("recvdate is null").Length = dt.Rows.Count Then
                Return 1
            ElseIf drs.Length = dt.Rows.Count Then
                toWorkcenterID = drs(0)("toworkcenterid").ToString()
                Return 2
            Else 'if (drs.Length < myDs.Tables(0).Rows.Count)
                toWorkcenterID = drs(0)("toworkcenterid").ToString()
                Return 3
            End If

        End If

    End Function

    ''' <summary>
    ''' 更新工区协作数据
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <remarks></remarks>
    Public Sub UpdateWorkCenterDispach(ByVal containerID As String) Implements IBusiness.uLMSContainercurrentinfoIBusiness.UpdateWorkCenterDispach
        Dim containerData = GetWorkCenterDispachInfo(containerID)
        For Each dr As DataRow In containerData.Rows
            Dim ID = dr("WORKCENTERDISPATCHID").ToString()
            If Convert.ToString(dr("CUWORKFLOWSTEPID")) <> Convert.ToString(dr("WORKFLOWSTEPID")) Then
                dr("WORKCENTERDISPATCHID") = ""
            End If
            If dr.IsNull("RECVEMPLOYEEID") = True Then '未接收
                dr("TOWORKCENTERID") = ""
            End If
            If Convert.ToInt64(dr("ISUSE")) = 1 Then '工区信息移除
                dr("WORKCENTERDISPATCHID") = ""
                dr("TOWORKCENTERID") = ""
                dr("WORKCENTERDISPATCHNUM") = 0
            End If
            '其他序工区协作控制 0:无协作的 1:协作未接收 2:全是协作已接收 3:部分协作已接收,还有协作未接收
            Dim toworkcenterID As String = ""
            Dim workcenterdispatchID As String = ""
            Dim n = IsDispachStep(dr, toworkcenterID, workcenterdispatchID)
            If n = 1 Or n = 3 Then
                dr("WORKCENTERDISPATCHNUM") = 1
            End If
            If String.IsNullOrWhiteSpace(dr("WORKCENTERDISPATCHID").ToString) Then
                dr("WORKCENTERDISPATCHID") = workcenterdispatchID '当前序才有
            End If
            If String.IsNullOrWhiteSpace(dr("TOWORKCENTERID").ToString) Then
                dr("TOWORKCENTERID") = toworkcenterID '接收才有，工序从前往后存
            End If
            '
            UpdateWorkCenterDispachOfRow(dr)
            UpdateTransferOfTable("CONTAINERWORKCENTERDISPATCH", "CONTAINERWORKCENTERDISPATCHID", ID) '更新工区表的结转标记

        Next
    End Sub
#End Region

#Region "更新批次转工信息 creat:Wangjh"
    ''' <summary>
    ''' 获取批次转工信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerShipInfo(ByVal containerID As String) As DataTable
        Dim strSql = <string>SELECT NVL(CSRE.CONTAINERSHIPRECORDID,CSRT.CONTAINERSHIPRECORDID) AS SHIPID,NVL(CSRE.CONTAINERSHIPRECORDID,CSRT.CONTAINERSHIPRECORDID) AS CCISHIPID,C.SHIPNUM,C.SHIPNUM CCISHIPNUM,
                                (CASE WHEN (CSR2.ISSHIP IS NULL) OR CSRT.ISUSE=1 OR C.SHIPNUM=8 THEN 0 ELSE 1 END) ISSHIP,C.CONTAINERID,CSRT.ISTRANSFER,CSRT.WORKFLOWSTEPID,
                                C.WORKFLOWSTEPID AS CUWORKFLOWSTEPID,CSRT.ISUSE CSRTISUSE,CSRE.ISUSE CSREISUSE,CSRT.PROPOSERID,CSRT.PREPOSERDATE,
                                CSRT.AUDITOR,CSRT.AUDITDATE,CSRT.AUDITRESULT,CSRT.SENDER,CSRT.TOACCEPTOR,CSRT.TOWORKCENTERID,NVL(CSRQ.SUMCompleteQty,0) SUMCompleteQty,
                                C.QTY,CSRE.RECIVER,DECODE(CSRT.SENDER,NULL, C.ORIGINALFACTORYID,CSRT.TOFACTORYID) FACTORYID,
                                FA.FACTORYNAME,DECODE(CSRT.TOWORKCENTERID,NULL,C.WORKCENTERID,CSRT.TOWORKCENTERID) WORKCENTERID,
                                WC.WORKCENTERNAME,CSRT.TOWORKFLOWSTEPID,CSRE.EMPLOYEEID AS COMPLETEID,CSRE.ISTRANSFER AS CSREISTRANSFER,
                                C.ORIGINALFACTORYID,FA2.FACTORYNAME AS ORIGINALFACTORYNAME,C.FROMWORKCENTERID,WC2.WORKCENTERNAME AS FROMWORKCENTERNAME, 
                                (CASE WHEN ws.islaststep=1 OR WS.DEFAULTPATHID IS NULL THEN 1 ELSE 0  END) cuislaststep,
                                cuw.factoryid cuworkflowfactoryid,C.CONTAINERNAME FROM CONTAINERSHIPRECORD CSRT 								
                                LEFT JOIN CONTAINERCURRENTINFO C ON CSRT.CONTAINERID = C.CONTAINERID
                                LEFT JOIN Workflowstep ws ON ws.workflowstepid=c.workflowstepid
                                LEFT JOIN CONTAINERSHIPRECIVE CSRE ON CSRE.CONTAINERSHIPRECORDID=CSRT.CONTAINERSHIPRECORDID AND csre.containerid=c.containerid 
                                LEFT JOIN (SELECT COUNT(CSRT2.CONTAINERSHIPRECORDID) ISSHIP,CSRT2.CONTAINERID,CSRSS.WORKFLOWSTEPID FROM  CONTAINERSHIPRECORD CSRT2--主制工厂已申请但未转入
                                            LEFT JOIN CONTAINERSHIPRECIVE CSRE2 ON CSRT2.CONTAINERSHIPRECORDID=CSRE2.CONTAINERSHIPRECORDID
                                            LEFT JOIN CONTAINERSHIPRECORDSTEP CSRSS ON CSRSS.CONTAINERSHIPRECORDID=CSRT2.CONTAINERSHIPRECORDID
                                            WHERE CSRT2.ISUSE=0 AND (CSRT2.PROPOSERID IS NOT NULL AND CSRT2.Auditresult IS NULL) OR (CSRT2.Auditresult=0 AND CSRE2.RECIVER IS NULL )
                                            GROUP BY CSRT2.CONTAINERID,CSRSS.WORKFLOWSTEPID) CSR2 ON CSR2.CONTAINERID=C.CONTAINERID  AND CSR2.WORKFLOWSTEPID=C.WORKFLOWSTEPID          
                                LEFT JOIN (SELECT NVL(SUM(CSRE.COMPLETEQTY) ,0) SUMCompleteQty,CSRE.CONTAINERSHIPRECORDID,csre.containerid
                                  FROM CONTAINERSHIPRECIVE CSRE GROUP BY CSRE.CONTAINERSHIPRECORDID,csre.containerid ) CSRQ ON
                        CSRQ.CONTAINERSHIPRECORDID=CSRT.CONTAINERSHIPRECORDID AND csrq.containerid=c.containerid           
                               LEFT JOIN FACTORY FA ON FA.FACTORYID=DECODE(CSRT.SENDER,NULL, C.ORIGINALFACTORYID,CSRT.TOFACTORYID)  
                                LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID=  DECODE(CSRT.TOWORKCENTERID,NULL,C.WORKCENTERID,CSRT.TOWORKCENTERID)  
                                LEFT JOIN FACTORY FA2 ON FA2.FACTORYID=C.ORIGINALFACTORYID
                                LEFT JOIN WORKCENTER WC2 ON WC2.WORKCENTERID=C.FROMWORKCENTERID    
                                LEFT JOIN workflowstep fws ON fws.workflowstepid=csrt.workflowstepid--转工申请的开始工序
                                LEFT JOIN Workflow cuw ON cuw.workflowid=c.workflowid--当前工艺     							          
                                WHERE C.containerstatus in (1,2) AND C.workflowstepid IS NOT NULL AND C.CONTAINERID IS NOT NULL AND csrt.containershiprecordid=NVL(c.shipid,csrt.containershiprecordid)
                                AND (CSRT.ISTRANSFER=0 OR (CSRT.ISTRANSFER=1 AND CSRE.ISTRANSFER=0) OR (C.WORKFLOWSTEPID=CSRT.TOWORKFLOWSTEPID AND (csrt.senddate IS not NULL OR C.SHIPNUM=2) AND csrt.isuse=0)) --或者已加工到当前工序
                                 </string>.Value
        strSql += " and c.CONTAINERID ='" + containerID + "' ORDER BY C.CONTAINERNAME,fws.sequence ASC"
        Dim dt1 = DatabaseHelper.Query_ReturnDatTable(strSql)
        '分批出来的卡，只可能有完成或转入记录
        strSql = <string>SELECT csre.containerid,cci.originalfactoryid,fa.factoryname AS ORIGINALFACTORYNAME,cci.fromworkcenterid,cci.fromworkcentername,csrq.SUMCompleteQty,cci.qty,CSRE.EMPLOYEEID AS COMPLETEID
,csre.reciver,csre.recivedate,csre.containershiprecordid shipid,csre.containershiprecordid CCIshipid,csre.isuse csrtisuse,(CASE WHEN csre.isuse=1 OR cci.shipnum=8 THEN 0 ELSE 1 END) isship,CCI.CONTAINERNAME FROM containercurrentinfo cci
LEFT JOIN (
SELECT NVL(SUM(CSRE.COMPLETEQTY) ,0) SUMCompleteQty,CSRE.Containerid
FROM CONTAINERSHIPRECIVE CSRE GROUP BY CSRE.Containerid
) CSRQ ON csrq.containerid=cci.containerid
LEFT JOIN containershiprecive csre ON csre.containerid=cci.containerid
LEFT JOIN CONTAINERSHIPRECORD CSR ON CSR.CONTAINERSHIPRECORDID=CSRE.CONTAINERSHIPRECORDID AND CSR.CONTAINERID=CSRE.CONTAINERID
LEFT JOIN factory fa ON fa.factoryid=cci.originalfactoryid
WHERE  CCI.workflowstepid IS NOT NULL AND csre.istransfer=0 AND CSR.CONTAINERSHIPRECORDID IS NULL
                 </string>.Value
        strSql += " and cci.CONTAINERID ='" + containerID + "'"
        Dim dt2 = DatabaseHelper.Query_ReturnDatTable(strSql)
        '
        dt1.Merge(dt2)
        Return dt1
    End Function

    ''' <summary>
    ''' 更新批次转工信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContainerShipOfRow(ByVal dr As DataRow) As Boolean
        Dim updateSql = <string>UPDATE CONTAINERCURRENTINFO C SET SHIPNUM=:SHIPNUM,ISSHIP=:ISSHIP,SHIPID=:SHIPID,FACTORYID=:FACTORYID, 
                                FACTORYNAME=:FACTORYNAME,WORKCENTERID=:WORKCENTERID,WORKCENTERNAME=:WORKCENTERNAME </string>.Value
        Dim updateParam(7) As OracleParameter
        updateParam(0) = DatabaseHelper.NewParameter(":CONTAINERID", dr("CONTAINERID"), OracleType.VarChar)
        If Convert.IsDBNull(dr("CSRTISUSE")) = False And Convert.ToInt16(dr("CSRTISUSE")) = 0 And Convert.IsDBNull(dr("PREPOSERDATE")) = False Then
            If Convert.IsDBNull(dr("AUDITOR")) Then
                dr("SHIPNUM") = 1 '已申请待审核
            End If
            If Convert.IsDBNull(dr("auditdate")) = False And Convert.IsDBNull(dr("SENDER")) Then
                dr("SHIPNUM") = 2 '已审核
                If Convert.ToInt16(dr("AUDITRESULT")) = 1 Then
                    dr("ShipNum") = 0
                    dr("ShipID") = ""
                    dr("IsShip") = 0
                End If
            End If

            If Not Convert.IsDBNull(dr("AUDITRESULT")) Then
                If Convert.IsDBNull(dr("SENDER")) And Convert.ToInt16(dr("AUDITRESULT")) = 0 Then
                    If Convert.ToString(dr("WORKFLOWSTEPID")) = Convert.ToString(dr("CUWORKFLOWSTEPID")) Then
                        dr("SHIPNUM") = 3 '待转出 需当前工序
                    End If
                End If
            End If

            If Convert.IsDBNull(dr("SENDER")) = False And Convert.IsDBNull(dr("TOACCEPTOR")) And Convert.IsDBNull(dr("ToWorkCenterID")) Then
                dr("SHIPNUM") = 4 '已转出待接收
            End If
            If Convert.IsDBNull(dr("RECIVER")) = False Then
                dr("FACTORYID") = dr("ORIGINALFACTORYID")
                dr("FACTORYNAME") = dr("ORIGINALFACTORYNAME")
                dr("WORKCENTERID") = dr("FROMWORKCENTERID")
                dr("WORKCENTERNAME") = dr("FROMWORKCENTERNAME")
                dr("SHIPID") = ""
                dr("SHIPNUM") = 8 '已转入
                dr("ISSHIP") = 0
            ElseIf Convert.IsDBNull(dr("TOACCEPTOR")) = False And Convert.IsDBNull(dr("ToWorkCenterID")) = False Then '接收后
                If Convert.ToInt64(dr("SUMCompleteQty")) < Convert.ToInt64(dr("QTY")) Then
                    dr("SHIPNUM") = 5 '已接收
                    If Convert.IsDBNull(dr("COMPLETEID")) And Convert.ToString(dr("CUWORKFLOWSTEPID")) = Convert.ToString(dr("TOWORKFLOWSTEPID")) Then '转工完成人为空且当前工序和转工的最后一道相同
                        dr("SHIPNUM") = 6 '待完成  
                    End If
                    If Convert.ToInt64(dr("SHIPNUM")) = 5 Then
                        updateSql += " ,TEAMNAME='',TEAMID='',TEAMDISPATCHID='',TEAMDISPATCHNUM=0"
                    End If
                End If
                If Convert.ToInt64(dr("SUMCompleteQty")) >= Convert.ToInt64(dr("QTY")) Then
                    If Convert.IsDBNull(dr("COMPLETEID")) = False And Convert.IsDBNull(dr("RECIVER")) Then
                        dr("SHIPNUM") = 7 '已完成未转入
                    End If
                End If
            End If
        Else
            If Convert.ToInt64(dr("SUMCompleteQty")) >= Convert.ToInt64(dr("QTY")) Then
                If Convert.IsDBNull(dr("COMPLETEID")) = False And Convert.IsDBNull(dr("RECIVER")) Then
                    dr("SHIPNUM") = 7 '已完成未转入
                End If
            End If
            If Convert.IsDBNull(dr("COMPLETEID")) = False And Convert.IsDBNull(dr("RECIVER")) = False Then
                dr("FACTORYID") = dr("ORIGINALFACTORYID")
                dr("FACTORYNAME") = dr("ORIGINALFACTORYNAME")
                dr("WORKCENTERID") = dr("FROMWORKCENTERID")
                dr("WORKCENTERNAME") = dr("FROMWORKCENTERNAME")
                dr("SHIPID") = ""
                dr("SHIPNUM") = 8 '已转入
                dr("ISSHIP") = 0 '已转入
            End If
        End If
        '规避掉可能出现的isship字段值错误
        If dr.IsNull("SHIPNUM") Or Val(dr("SHIPNUM").ToString()) = 8 Then
            dr("SHIPNUM") = 0
            dr("ISSHIP") = 0
        End If
        If Val(dr("ISSHIP").ToString()) = 0 And Val(dr("SHIPNUM").ToString()) < 8 And Val(dr("SHIPNUM").ToString()) > 0 Then
            dr("ISSHIP") = 1
        End If
        '
        If dr("SHIPNUM").ToString() = dr("CCISHIPNUM").ToString() Then
            Return True
        End If
        updateSql += " WHERE C.CONTAINERID=:CONTAINERID"
        updateParam(1) = DatabaseHelper.NewParameter(":SHIPID", dr("SHIPID"), OracleType.VarChar)
        updateParam(2) = DatabaseHelper.NewParameter(":SHIPNUM", dr("SHIPNUM"), OracleType.Number)
        updateParam(3) = DatabaseHelper.NewParameter(":ISSHIP", dr("ISSHIP"), OracleType.Number)
        updateParam(4) = DatabaseHelper.NewParameter(":FACTORYID", dr("FACTORYID"), OracleType.VarChar)
        updateParam(5) = DatabaseHelper.NewParameter(":FACTORYNAME", dr("FACTORYNAME"), OracleType.VarChar)
        updateParam(6) = DatabaseHelper.NewParameter(":WORKCENTERID", dr("WORKCENTERID"), OracleType.VarChar)
        updateParam(7) = DatabaseHelper.NewParameter(":WORKCENTERNAME", dr("WORKCENTERNAME"), OracleType.VarChar)
        Dim i = DatabaseHelper.ExecuteSql(updateSql, updateParam)
        ' "更新总表记录"
        Dim sql = LogHelp.TranferSql(updateSql, updateParam)
        InsertOracleLog(System.Reflection.MethodBase.GetCurrentMethod().Name, "更新转工信息", i, sql, dr("CONTAINERNAME").ToString())

        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    ''' <summary>
    ''' 处理数据
    ''' </summary>
    ''' <param name="drs"></param>
    ''' <param name="contianerLi"></param>
    ''' <remarks></remarks>
    Sub LoopContainerShipData(ByVal drs As DataRow(), ByVal contianerLi As List(Of String))
        For Each dr In drs
            If contianerLi.Contains(dr("containerid").ToString()) Then
                Continue For
            Else
                contianerLi.Add(dr("containerid").ToString())
            End If
            If Convert.IsDBNull(dr("CSRTISUSE")) = False Then
                If Convert.ToInt64(dr("CSRTISUSE")) = 1 Then
                    '自动转工撤回或者删除时还原相关字段
                    dr("SHIPID") = ""
                    dr("SHIPNUM") = 0
                    dr("FACTORYID") = dr("ORIGINALFACTORYID")
                    dr("FACTORYNAME") = dr("ORIGINALFACTORYNAME")
                    dr("WORKCENTERID") = dr("FROMWORKCENTERID")
                    dr("WORKCENTERNAME") = dr("FROMWORKCENTERNAME")
                End If
            End If

            If UpdateContainerShipOfRow(dr) = False Then
                Continue For
            End If
            If Convert.ToString(dr("CCISHIPID")) <> "" Then
                Dim tableName = "CONTAINERSHIPRECORD"
                Dim ID = "CONTAINERSHIPRECORDID"
                Dim IDValue = dr("CCISHIPID").ToString
                If dr("ISTRANSFER").ToString() = "0" Then
                    UpdateTransferOfTable(tableName, ID, IDValue)
                End If
                If Convert.ToString(dr("CSREISTRANSFER")) = "0" Then
                    UpdateTransferOfTable("CONTAINERSHIPRECIVE", ID, IDValue)
                End If
            End If

        Next
    End Sub

    ''' <summary>
    ''' 更新批次转工信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <remarks></remarks>
    Public Sub UpdateContainerShip(ByVal containerID As String)
        Dim containerData = GetContainerShipInfo(containerID)
        Dim contianerLi As New List(Of String)
        Dim drs1 = containerData.Select("ISTRANSFER=0 or CSREISTRANSFER=0")
        Dim drs2 = containerData.Select("(ISTRANSFER<>0 OR ISTRANSFER IS NULL) and (CSREISTRANSFER<>0 OR CSREISTRANSFER IS NULL)")
        LoopContainerShipData(drs1, contianerLi)
        LoopContainerShipData(drs2, contianerLi)
    End Sub
#End Region

#Region "删除派工记录逻辑处理 add by tianFW 2019年1月16日"
    ''' <summary>
    ''' 获取需要删除的派工单信息
    ''' </summary>
    ''' <param name="containerStepDispatchID">派工记录唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDelDispatchInfo(ByVal containerStepDispatchID As String) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT  CSD.CONTAINERID,CSD.CONTAINERSTEPDISPATCHID,CCI.WORKFLOWSTEPID,CCI.WORKFLOWSTEPSEQUENCE")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCH CSD")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID = CSD.CONTAINERID")
        strsql.AppendFormat("WHERE CSD.CONTAINERSTEPDISPATCHID='{0}'", containerStepDispatchID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取批次以及工序序号sequence当前及之后的批次
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="sequence"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerOtherDispatchInfo(ByVal containerID As String, ByVal sequence As Integer) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSD.WORKFLOWSTEPID FROM CONTAINERSTEPDISPATCH CSD")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
        strsql.AppendFormat(" WHERE CSD.CONTAINERID = '{0}' AND CSD.ISUSE=0 AND WS.SEQUENCE>={1} ", containerID, sequence)
        strsql.AppendLine("ORDER BY CSD.WORKFLOWSTEPID")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

#Region "yuxj20220826热表删除派工数据，特别处理"
    ''' <summary>
    ''' yuxj20220826获取需要删除的派工单信息
    ''' </summary>
    ''' <param name="containerStepDispatchID">派工记录唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDelDispatchInfo2(ByVal containerStepDispatchID As String) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT  CSD.CONTAINERID,CSD.CONTAINERSTEPDISPATCHID,CCI.WORKFLOWSTEPID,CCI.WORKFLOWSTEPSEQUENCE")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCH2 CSD")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID = CSD.CONTAINERID")
        strsql.AppendFormat("WHERE CSD.CONTAINERSTEPDISPATCHID='{0}'", containerStepDispatchID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' yuxj20220826获取批次以及工序序号sequence当前及之后的批次
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="sequence"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerOtherDispatchInfo2(ByVal containerID As String, ByVal sequence As Integer) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSD.WORKFLOWSTEPID FROM CONTAINERSTEPDISPATCH2 CSD")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
        strsql.AppendFormat(" WHERE CSD.CONTAINERID = '{0}' AND CSD.ISUSE=0 AND WS.SEQUENCE>={1} ", containerID, sequence)
        strsql.AppendLine("ORDER BY CSD.WORKFLOWSTEPID")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function
#End Region

    ''' <summary>
    ''' 更新批次服务总表派工状态
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepDispatchOfRow(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERCURRENTINFO C SET")
        strsql.AppendFormat(" C.DISPATCHNUM ={0},", para("dispatchNum"))
        strsql.AppendFormat(" C.Isdispatch ={0}", para("isDispatch"))
        strsql.AppendFormat(" WHERE C.CONTAINERID='{0}'", para("containerID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取并处理删除的批次记录逻辑
    ''' </summary>
    ''' <param name="containerStepDispatchID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAndDoWithDispatchDelInfo(ByVal containerStepDispatchID As String) As Boolean
        GetAndDoWithDispatchDelInfo = False
        Dim dt As DataTable = GetDelDispatchInfo(containerStepDispatchID)
        If dt.Rows.Count = 0 Then
            GetAndDoWithDispatchDelInfo = True
            Exit Function
        End If
        Dim dr As DataRow = dt.Rows(0)
        Dim otherdt As DataTable = GetContainerOtherDispatchInfo(dr("CONTAINERID"), Integer.Parse(dr("WORKFLOWSTEPSEQUENCE")))
        Dim para = New Dictionary(Of String, String)
        '如果没有其他派工记录，则将DisPatchNum和IsDisPatch置为0
        If otherdt.Rows.Count = 0 Then
            para.Add("dispatchNum", "0")
            para.Add("isDispatch", "0")
            para.Add("containerID", dr("CONTAINERID").ToString())
        Else
            For Each r As DataRow In otherdt.Rows
                '如果存在当前序派工记录，则不用再更新状态（原来批次派工标识就是 IsDisPatch=1 DisPatchNum=1）
                If r("WORKFLOWSTEPID") = dr("WORKFLOWSTEPID") Then
                    Exit Function
                End If
            Next
            '存在派工，但是当前序不存在派工
            para.Add("dispatchNum", "1")
            para.Add("isDispatch", "0")
            para.Add("containerID", dr("CONTAINERID").ToString())
        End If
        If Not UpdateStepDispatchOfRow(para) Then
            Exit Function
        End If
        GetAndDoWithDispatchDelInfo = True
    End Function
#End Region

#Region "删除报工记录逻辑处理 add by tianFW 2019年1月17日"
    ''' <summary>
    ''' 获取删除报工记录信息
    ''' </summary>
    ''' <param name="containerStepWorkReportID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDelWorkReportInfo(ByVal containerStepWorkReportID As String) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSW.CONTAINERID,CSW.WORKFLOWSTEPID,CSW.CHECKTYPE")
        strsql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendFormat(" WHERE CSW.Containerstepworkreportid = {0}", containerStepWorkReportID)
        strsql.AppendLine("GROUP BY CSW.CONTAINERID,CSW.WORKFLOWSTEPID,CSW.CHECKTYPE")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取批次工序的其他没有检验记录的有效报工记录
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowStepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetOtherWorkReportInfo(ByVal containerID As String, ByVal workflowStepID As String) As DataTable
        Dim strsql As New StringBuilder
        Dim strvalue As String =
            <string>
                SELECT CSW.CONTAINERSTEPWORKREPORTID,CCI.CONTAINERCHECKINFOID,CCIO.CONTAINERFINISHEDCHECKINFOID,CSW.CHECKTYPE
	         FROM CONTAINERSTEPWORKREPORT CSW
	         LEFT JOIN CONTAINERCHECKINFO CCI ON CCI.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID AND CCI.ISUSE = 1
	         LEFT JOIN CONTAINERFINISHEDCHECKINFO CCIO ON CCIO.CONTAINERFINISHEDCHECKINFOID = CSW.CONTAINERFINISHEDCHECKINFOID AND CCIO.ISUSE = 1
	         WHERE CCI.CONTAINERCHECKINFOID IS NULL AND CCIO.CONTAINERFINISHEDCHECKINFOID IS NULL AND CSW.ISUSE = 0
            </string>
        strsql.AppendLine(strvalue)
        strsql.AppendFormat(" AND CSW.CONTAINERID = '{0}' AND CSW.WORKFLOWSTEPID='{1}'", containerID, workflowStepID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 更新首检或工序检标识
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepOrFirstCheckNum(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim strsql As New StringBuilder
        Dim strValue As String = ""
        strsql.AppendLine("UPDATE CONTAINERCURRENTINFO C SET")
        If para.ContainsKey("firstCheckNum") Then
            strValue += String.Format(",C.FIRSTCHECKNUM = {0}", para("firstCheckNum"))
        End If
        If para.ContainsKey("stepCheckNum") Then
            strValue += String.Format(",C.STEPCHECKNUM = {0}", para("stepCheckNum"))
        End If
        strsql.AppendLine(strValue.Substring(1))
        strsql.AppendFormat(" WHERE C.CONTAINERID = '{0}'", para("containerID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 根据批次报工删除记录更新批次服务总表StepCheckNum标识
    ''' </summary>
    ''' <param name="ContainerStepWorkReportID">报工唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepCheckNumWithWorkReportInfo(ByVal ContainerStepWorkReportID As String) As Boolean
        UpdateStepCheckNumWithWorkReportInfo = False
        Dim dt As DataTable = GetDelWorkReportInfo(ContainerStepWorkReportID)
        If dt.Rows.Count = 0 Then
            UpdateStepCheckNumWithWorkReportInfo = True
            Exit Function
        End If
        Dim newdt As DataTable = dt.DefaultView.ToTable(True, "CONTAINERID", "WORKFLOWSTEPID")
        Dim otherdt As DataTable
        Dim para As Dictionary(Of String, String)
        Dim IsExistFirstCheckNum As Boolean = False
        Dim IsExistStepCheckNum As Boolean = False
        For Each r As DataRow In newdt.Rows
            IsExistFirstCheckNum = False
            IsExistStepCheckNum = False
            para = New Dictionary(Of String, String)
            otherdt = GetOtherWorkReportInfo(r("CONTAINERID"), r("WORKFLOWSTEPID"))
            para.Add("containerID", r("CONTAINERID"))
            If otherdt.Rows.Count = 0 Then '没有其他没有检验的有效报工记录，则全部置为0
                para.Add("firstCheckNum", "0")
                para.Add("stepCheckNum", "0")
            Else
                For Each dr As DataRow In otherdt.Rows
                    If dr("CHECKTYPE") = "1" And IsExistFirstCheckNum = False Then '存在首检
                        IsExistFirstCheckNum = True
                    ElseIf (dr("CHECKTYPE") = "2" Or dr("CHECKTYPE") = "3") And IsExistStepCheckNum = False Then '存在其他检或返修检
                        IsExistStepCheckNum = True
                    End If
                    If IsExistFirstCheckNum And IsExistStepCheckNum Then
                        Exit For
                    End If
                Next
                If Not IsExistFirstCheckNum Then
                    para.Add("firstCheckNum", "0")
                End If
                If Not IsExistStepCheckNum Then
                    para.Add("stepCheckNum", "0")
                End If
            End If
            If para.ContainsKey("firstCheckNum") Or para.ContainsKey("stepCheckNum") Then
                UpdateStepOrFirstCheckNum(para)
            End If
        Next
        UpdateStepCheckNumWithWorkReportInfo = True
    End Function
#End Region

#Region "删除工序检验记录 add by tianFW 2019年1月17日"
    ''' <summary>
    ''' 获取删除的检验记录
    ''' </summary>
    ''' <param name="containerStepCheckInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDelStepCheckInfo(ByVal containerStepCheckInfoID As String) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CCI.CONTAINERID,CCI.WORKFLOWSTEPID,CSW.CHECKTYPE")
        strsql.AppendLine("FROM CONTAINERCHECKINFO CCI")
        strsql.AppendLine("LEFT JOIN CONTAINERSTEPWORKREPORT CSW ON CSW.CONTAINERSTEPWORKREPORTID = CCI.CONTAINERSTEPWORKREPORTID")
        strsql.AppendFormat(" WHERE CCI.CONTAINERCHECKINFOID = '{0}' ", containerStepCheckInfoID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 删除检验记录（之前）更新首检或工序检状态
    ''' </summary>
    ''' <param name="containerStepCheckInfoID">检验记录唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepOrFirstCheckNumWithDelCheckInfo(ByVal containerStepCheckInfoID As String) As Boolean
        UpdateStepOrFirstCheckNumWithDelCheckInfo = False
        Dim dt As DataTable = GetDelStepCheckInfo(containerStepCheckInfoID)
        If dt.Rows.Count = 0 Then
            Exit Function
        End If
        Dim dr As DataRow = dt.Rows(0)
        Dim para = New Dictionary(Of String, String)
        para.Add("containerID", dr("CONTAINERID"))
        If dr("CHECKTYPE") = "1" Then '删除的工序记录是首检工序记录
            para.Add("firstCheckNum", "1")
        ElseIf dr("CHECKTYPE") = "2" Then '删除的工序记录是工序检记录
            para.Add("stepCheckNum", "1")
        Else
            para.Add("stepCheckNum", "1")
        End If
        UpdateStepOrFirstCheckNum(para)
        UpdateStepOrFirstCheckNumWithDelCheckInfo = True
    End Function
#End Region

#Region "删除成品检验记录"
    ''' <summary>
    ''' 获取删除的成品检验记录
    ''' </summary>
    ''' <param name="containerFinishCheckInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDelFinishCheckInfo(ByVal containerFinishCheckInfoID As String) As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CFC.CONTAINERID,CFC.WORKFLOWSTEPID")
        strsql.AppendLine("FROM CONTAINERFINISHEDCHECKINFO CFC")
        strsql.AppendFormat(" WHERE CFC.CONTAINERFINISHEDCHECKINFOID = '{0}'", containerFinishCheckInfoID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 删除检验记录（之前）更新首检或工序检状态
    ''' </summary>
    ''' <param name="containerFinishCheckInfoID">检验记录唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepOrFirstCheckNumWithDelFinishCheckInfo(ByVal containerFinishCheckInfoID As String) As Boolean
        UpdateStepOrFirstCheckNumWithDelFinishCheckInfo = False
        Dim dt As DataTable = GetDelFinishCheckInfo(containerFinishCheckInfoID)
        If dt.Rows.Count = 0 Then
            Exit Function
        End If
        Dim isExistFirstCheckNum As Boolean = False
        Dim isExistStepCheckNum As Boolean = False
        Dim dr As DataRow = dt.Rows(0)
        Dim para = New Dictionary(Of String, String)
        para.Add("containerID", dr("CONTAINERID"))
        para.Add("stepCheckNum", "1")
        UpdateStepOrFirstCheckNum(para)
        UpdateStepOrFirstCheckNumWithDelFinishCheckInfo = True
    End Function

#End Region


#End Region

#Region "子卡异常问题处理"
    ''' <summary>
    ''' 处理子卡异常导致的两种问题处理（暂定只处理热表）
    ''' </summary>
    ''' <param name="containerName">批次</param>
    ''' <returns></returns>
    ''' <remarks>1、热表子卡工步全部检验完成，但未入库且没有工序检验。2、热表子卡干完，但是主制工序未有自动报工数据</remarks>
    Public Function DealSubContainerError(ByVal containerName As String) As uMESProcessResult Implements IBusiness.uLMSContainercurrentinfoIBusiness.DealSubContainerError
        Dim result As New uMESProcessResult
        Try
            Dim sql = String.Format(<string>SELECT DISTINCT c.containerid,c.containername,c.workflowstepid,c.qty,c.workflowstepsequence,c.workflowid,c.containertype,c1.qualificationnum,
                                cc.containerid subcontainerid,cc.containername subcontainername,cc.isinstore,cc.containerstatus
                                FROM containercurrentinfo c
                                LEFT JOIN container c1 ON c1.containerid = c.containerid
                                LEFT JOIN containersubcontainer csc ON csc.containerid = c.containerid AND (csc.workflowstepid = c.workflowstepid OR csc.workflowstepname = c.workflowstepname)
                                LEFT JOIN containercurrentinfo cc ON cc.containerid = csc.subcontainerid AND cc.containerstatus = 1
                                WHERE c.containername = '{0}' AND c.containerstatus = 1 AND c.isinstore = 0
                                AND CASE WHEN csc.subcontainerid IS NULL THEN c.containerid ELSE cc.containerid END IS NOT NULL</string>.Value, containerName)

            Dim dtContainer = DatabaseHelper.Query_ReturnDatTable(sql)
            If dtContainer.Rows.Count = 0 Then
                result.Message = "批次未在制或者当前序无子卡批次，请重新查询"
                result.Result = False
                Return result
                Exit Function
            End If
            Dim msg = String.Empty
            If String.IsNullOrWhiteSpace(dtContainer.Rows(0)("subcontainerid").ToString()) Then
                '如果批次当前序没有子卡
                result = SaveStepInfoProduceInfo(dtContainer)
            Else
                '如果当前序存在子卡，则查看子卡是否完工，转工序是否有报工记录
                result = SaveStepAutoProduceInfo(dtContainer)
            End If
        Catch ex As Exception
            result.Result = False
            result.Message = "操作出错，原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 保存工步的工序生产信息（工步检验完，但工序未检验且未入库）
    ''' </summary>
    ''' <param name="dtContainer"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SaveStepInfoProduceInfo(ByVal dtContainer As DataTable) As uMESProcessResult
        Dim result As New uMESProcessResult
        Dim containerID = dtContainer.Rows(0)("containerid").ToString() '批次ID
        Dim workflowstepID = dtContainer.Rows(0)("workflowstepid").ToString() '批次当前在制工序
        Dim qty = Integer.Parse(dtContainer.Rows(0)("qty").ToString()) '批次在制数
        '查询检验的版本
        Dim sql = String.Format(<string>
                                        SELECT s.revision 
                                        FROM containercheckinfo2 cci 
                                        LEFT JOIN stepinfo s ON s.stepinfoid = cci.stepinfoid
                                        WHERE cci.containerid = '{0}' AND cci.workflowstepid = '{1}' AND cci.isuse = 0
                                </string>.Value, containerID, workflowstepID)
        Dim dtRev = DatabaseHelper.Query_ReturnDatTable(sql)
        If dtRev.Rows.Count = 0 Then
            result.Message = "批次工序不存在工步检验"
            result.Result = False
            Return result
            Exit Function
        End If
        '查询所有工步的检验信息
        sql = String.Format(<string>SELECT SUM(nvl(cci.checkqty,0)) checkqty,SUM(nvl(cci.acceptanceqty,0)) acceptanceqty,
                                    SUM(nvl(cci.lossqty,0)) lossqty,SUM(nvl(cci.defectqty,0)) defectqty,SUM(nvl(cci.reworkqty,0)) reworkqty
                                    FROM stepinfo s 
                                    LEFT JOIN containercheckinfo2 cci ON cci.containerid = '{0}' AND cci.workflowstepid = '{1}' AND cci.isuse = 0
                                    AND cci.stepinfoid = s.stepinfoid
                                    WHERE s.workflowstepid = '{1}' AND s.revision = '{2}'
                                    GROUP BY s.stepinfoid</string>.Value, containerID, workflowstepID, dtRev.Rows(0)("revision").ToString())
        Dim dtCheck = DatabaseHelper.Query_ReturnDatTable(sql)

        If dtCheck.Rows.Count = 0 Then
            result.Message = "批次工序不存在工步"
            result.Result = False
            Return result
            Exit Function
        End If

        '如果存在工步未报完工
        If dtCheck.AsEnumerable().Where(Function(p) Integer.Parse(p("checkqty").ToString()) < qty).Count > 0 Then
            result.Message = "批次工步未完工"
            result.Result = False
            Return result
            Exit Function
        End If
        sql = String.Format("SELECT 1 FROM containercheckinfo cci WHERE cci.containerid = '{0}' AND cci.workflowstepid = '{1}' AND cci.isuse = 0", containerID, workflowstepID)
        '如果不存在工序检验，则插入一条工序检验
        If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
            result.Message = "批次已存在检验"
            result.Result = False
            Return result
            Exit Function
        End If

        Dim lossQty = Integer.Parse(dtCheck.Compute("SUM(LOSSQTY)", String.Empty).ToString()) '工序下所有工步报废数总和
        Dim defectqty = Integer.Parse(dtCheck.Compute("SUM(defectqty)", String.Empty).ToString()) '工序下所有工步超差数总和
        Dim reworkqty = Integer.Parse(dtCheck.Compute("SUM(reworkqty)", String.Empty).ToString()) '工序下所有工步返修数总和
        Dim accepttanceQty = Integer.Parse(dtCheck.Compute("Min(Acceptanceqty)", "true").ToString()) '最小的合格数
        Dim sqlList As New ArrayList
        sql = String.Format(<string>SELECT cci.checkerid,cci.factoryid,to_char(cci.createdate,'yyyy-MM-dd hh24:mi:ss') createdate 
                                            FROM containercheckinfo2 cci
                                            LEFT JOIN stepinfo s ON s.stepinfoid = cci.stepinfoid
                                            WHERE cci.containerid = '{0}' AND cci.workflowstepid = '{1}' AND cci.isuse = 0
                                            ORDER BY s.stepseq,cci.createdate DESC</string>.Value, containerID, workflowstepID)
        Dim dtStepInfoCheck = DatabaseHelper.Query_ReturnDatTable(sql) '查询出工步检验记录，取最新一条插入工序检验
        Dim drCheck = dtStepInfoCheck.Rows(0)
        '查询工序的报工信息
        sql = String.Format("SELECT csw.containerstepworkreportid FROM containerstepworkreport csw WHERE csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.isuse = 0", containerID, workflowstepID)
        Dim dtWorkReport = DatabaseHelper.Query_ReturnDatTable(sql) '获取工序报工
        Dim containerStepWorkReportID = String.Empty '报工ID
        '如果不存在工序报工，则要添加一条工序报工
        If dtWorkReport.Rows.Count = 0 Then
            sql = String.Format(<string>SELECT to_char(csw.createdate,'yyyy-MM-dd hh24:mi:ss') createdate,csw.factoryid,csw.workerid 
                                            FROM containerstepworkreport2 csw
                                            LEFT JOIN stepinfo s ON s.stepinfoid = csw.stepinfoid
                                            WHERE csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.isuse = 0
                                            ORDER BY s.stepseq,csw.createdate DESC</string>.Value, containerID, workflowstepID)
            Dim dtStepInfoReport = DatabaseHelper.Query_ReturnDatTable(sql) '查询出工步报工记录，取最新一条插入工序报工
            Dim drReport = dtStepInfoReport.Rows(0)
            sql = String.Format("SELECT csd.containerstepdispatchid FROM containerstepdispatch csd WHERE csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0", containerID, workflowstepID)
            Dim dtDispatch = DatabaseHelper.Query_ReturnDatTable(sql) '获取工序派工
            Dim containerStepDispatchID = String.Empty '工序派工ID
            '如果不存在工序派工，则要添加一条工序派工
            If dtDispatch.Rows.Count = 0 Then
                containerStepDispatchID = Guid.NewGuid.ToString()
                sql = String.Format(<string>SELECT to_char(csd.createdate,'yyyy-MM-dd hh24:mi:ss') createdate,to_char(csd.plannedstartdate,'yyyy-MM-dd') plannedstartdate,
                                            to_char(csd.plannedfinishdate,'yyyy-MM-dd') plannedfinishdate,csd.employeeid,
                                            csd.factoryid,csd.qty,csd.resourceid,csd.workcenterid,csd.teamid  FROM containerstepdispatch2 csd
                                            LEFT JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid
                                            WHERE csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0
                                            ORDER BY s.stepseq,csd.createdate DESC</string>.Value, containerID, workflowstepID)
                Dim dtStepInfoDis = DatabaseHelper.Query_ReturnDatTable(sql) '查询出工步派工记录，取最新一条插入工序派工
                Dim drDis = dtStepInfoDis.Rows(0)
                '插入工序派工
                sql = String.Format(<string>INSERT INTO containerstepdispatch(containerid,containerstepdispatchid,createdate,employeeid,factoryid,isuse,qty,resourceid,teamid,workcenterid,workerid,workflowstepid,plannedstartdate,plannedfinishdate)
                                      VALUES('{0}','{1}',to_date('{2}','yyyy-MM-dd hh24:mi:ss'),'{3}','{4}',0,'{5}','{6}','{7}','{8}','{9}','{10}',to_date('{11}','yyyy-MM-dd'),to_date('{12}','yyyy-MM-dd'))</string>.Value,
                                  containerID, containerStepDispatchID, drDis("createdate").ToString(), drDis("employeeid").ToString(), drDis("factoryid").ToString(), drDis("qty").ToString(),
                                  drDis("resourceid").ToString(), drDis("teamid").ToString(), drDis("workcenterid").ToString(), drReport("workerid").ToString(), workflowstepID, drDis("plannedstartdate").ToString(), drDis("plannedfinishdate").ToString())
                sqlList.Add(sql)
            Else
                containerStepDispatchID = dtDispatch.Rows(0)("containerstepdispatchid").ToString()
            End If
            '插入工序报工
            containerStepWorkReportID = Guid.NewGuid.ToString()
            sql = String.Format(<string>INSERT INTO containerstepworkreport(checktype,containerid,containerstepdispatchid,containerstepworkreportid,createdate,factoryid,isuse,qty,workerid,workflowstepid,lossqty)
                                                VALUES('2','{0}','{1}','{2}',to_date('{3}','yyyy-MM-dd hh24:mi:ss'),'{4}',0,{5},'{6}','{7}',{8})</string>.Value,
                                containerID, containerStepDispatchID, containerStepWorkReportID, drReport("createdate").ToString(), drReport("factoryid").ToString(), accepttanceQty + lossQty, drReport("workerid").ToString(),
                                workflowstepID, lossQty)
            sqlList.Add(sql)
        Else
            containerStepWorkReportID = dtWorkReport.Rows(0)("containerstepworkreportid").ToString()
        End If
        '插入工序检验
        sql = String.Format(<string>INSERT INTO containercheckinfo(acceptanceqty,checkerid,checkqty,containercheckinfoid,containerid,createdate,defectqty,factoryid,isuse,lossqty,reworkqty,workflowstepid,containerstepworkreportid,acceptanceqty2)
                                            VALUES({0},'{1}',{2},'{3}','{4}',to_date('{5}','yyyy-MM-dd hh24:mi:ss'),{6},'{7}',0,{8},{9},'{10}','{11}',{12})</string>.Value,
                                accepttanceQty, drCheck("checkerid").ToString(), accepttanceQty + lossQty, Guid.NewGuid.ToString(), containerID, drCheck("createdate").ToString(), defectqty,
                                drCheck("factoryid").ToString(), lossQty, reworkqty, workflowstepID, containerStepWorkReportID, accepttanceQty - defectqty - reworkqty)
        sqlList.Add(sql)
        DatabaseHelper.ExecuteSqlTran(sqlList)
        result.Result = True
        result.Message = "保存成功"
        Return result
    End Function

    ''' <summary>
    ''' 保存转工序的自动生产信息（子卡干完，但是主制工序未有自动报工数据）
    ''' </summary>
    ''' <param name="dtContainer"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SaveStepAutoProduceInfo(ByVal dtContainer As DataTable) As uMESProcessResult
        Dim result As New uMESProcessResult
        Dim containerID = dtContainer.Rows(0)("containerid").ToString() '批次ID
        Dim workflowstepID = dtContainer.Rows(0)("workflowstepid").ToString() '批次当前在制工序
        If dtContainer.Rows(0)("isinstore").ToString() <> "1" Then
            result.Message = String.Format("批次当前序存在未完工的子卡（{0}）", dtContainer.Rows(0)("subcontainername").ToString())
            result.Result = False
            Return result
            Exit Function
        End If
        If dtContainer.Rows(0)("containerstatus").ToString() <> "1" Then
            result.Message = String.Format("批次当前序的子卡批次状态无效（{0}）", dtContainer.Rows(0)("subcontainername").ToString())
            result.Result = False
            Return result
            Exit Function
        End If
        Dim sql = String.Format("SELECT 1 FROM containercheckinfo cci WHERE cci.containerid = '{0}' AND cci.workflowstepid = '{1}' AND cci.isuse = 0", containerID, workflowstepID)
        If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
            result.Message = "批次当前序已存在检验"
            result.Result = False
            Return result
            Exit Function
        End If

        Dim subContainerID = dtContainer.Rows(0)("subcontainerid").ToString() '子卡批次ID
        sql = String.Format(<string>SELECT Row_Number() OVER(ORDER BY ws.sequence DESC) numer,SUM(NVL(csr.checkqty,0)) checkqty,
                                    SUM(nvl(csr.acceptanceqty,0)) passqty,SUM(nvl(csr.lossqty,0)) lossqty,SUM(nvl(csr.acceptanceqty2,0)) acceptanceqty2,
                                    SUM(nvl(csr.defectqty,0)) defectqty,SUM(nvl(csr.reworkqty,0)) reworkqty,csr.workflowstepid,ws.sequence
                                    FROM containercheckinfo csr 
                                    left JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
                                    WHERE csr.containerid = '{0}' AND csr.isuse = 0
                                    GROUP BY csr.workflowstepid,ws.sequence</string>.Value, subContainerID)
        'sql = String.Format(<string>SELECT Row_Number() OVER(ORDER BY ws.sequence DESC) numer,
        '                            SUM(nvl(csr.passqty,0)) passqty,SUM(nvl(csr.lossqty,0)) lossqty,SUM(nvl(csr.reportqty,0)) reportqty,
        '                            SUM(nvl(csr.defectqty,0)) defectqty,SUM(nvl(csr.reworkqty,0)) reworkqty,csr.workflowstepid,ws.sequence
        '                            FROM containersteprecord csr 
        '                            left JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
        '                            WHERE csr.containerid = '{0}' AND csr.usefulstatus = '1' AND csr.usecompleted = '1'
        '                            GROUP BY csr.workflowstepid,ws.sequence</string>.Value, subContainerID)
        '取最大工序的报工数据
        Dim dtSubReport = DatabaseHelper.Query_ReturnDatTable(sql)
        If dtSubReport.Rows.Count = 0 Then
            result.Message = String.Format("对应子卡未存在报工（{0}）", dtContainer.Rows(0)("subcontainername").ToString())
            result.Result = False
            Return result
            Exit Function
        End If
        Dim passqty = Integer.Parse(dtSubReport.Rows(0)("passqty").ToString()) '合格数
        Dim lossqty = dtSubReport.AsEnumerable().Sum(Function(p) Integer.Parse(p("lossqty").ToString())) '所有工序的报废数
        Dim defectqty = dtSubReport.AsEnumerable().Sum(Function(p) Integer.Parse(p("defectqty").ToString())) '所有工序的超差数
        Dim reworkqty = dtSubReport.AsEnumerable().Sum(Function(p) Integer.Parse(p("reworkqty").ToString())) '所有工序的返修数
        If dtContainer.Rows(0)("containertype").ToString() = "7" And (passqty + lossqty) > 1 Then '如果主卡是单卡，而子卡是多个单卡的子卡，则要单独判断
            passqty = 1
            If lossqty > 0 Then
                sql = String.Format("SELECT c.qualificationnum FROM container c WHERE c.parentcontainerid = '{0}' AND c.status = 2 AND c.qty = 0", subContainerID)
                Dim dtChildSubContainer = DatabaseHelper.Query_ReturnDatTable(sql)
                If dtChildSubContainer.Select(String.Format("qualificationnum='{0}'", dtContainer.Rows(0)("qualificationnum").ToString())).Length > 0 Then
                    passqty = 0
                    lossqty = 1
                Else
                    lossqty = 0
                End If
            End If
        End If

        '取最大工序的报工、检验明细
        sql = String.Format(<stirng>
                                SELECT to_char(csr.checkdate,'yyyy-MM-dd hh24:mi:ss') checkdate,csr.checkemployeeid,
                                to_char(csr.reportdate,'yyyy-MM-dd hh24:mi:ss') reportdate,csr.reportemployeeid,c.originalfactoryid
                                FROM containercurrentinfo c
                                LEFT JOIN containersteprecord csr ON csr.containerid = c.containerid AND csr.workflowstepid = '{1}'
                                WHERE c.containerid = '{0}' AND csr.usefulstatus = '1' AND csr.usecompleted = '1'
                                ORDER BY csr.createdate DESC 
                            </stirng>.Value, subContainerID, dtSubReport.Rows(0)("workflowstepid").ToString())
        Dim dtLastSubReport = DatabaseHelper.Query_ReturnDatTable(sql)
        Dim checkdate = dtLastSubReport.Rows(0)("checkdate").ToString() '检验时间
        Dim checkemployeeid = dtLastSubReport.Rows(0)("checkemployeeid").ToString() '检验人
        Dim reportdate = dtLastSubReport.Rows(0)("reportdate").ToString() '报工时间
        Dim reportemployeeid = dtLastSubReport.Rows(0)("reportemployeeid").ToString() '报工人
        Dim subFactoryid = dtLastSubReport.Rows(0)("originalfactoryid").ToString() '子卡分厂
        Dim sqlList As New ArrayList
        '取主卡工序的报工数据
        sql = String.Format("SELECT csw.containerstepworkreportid FROM containerstepworkreport csw WHERE csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.isuse = 0", containerID, workflowstepID)
        Dim dtWorkReport = DatabaseHelper.Query_ReturnDatTable(sql) '获取工序报工
        Dim containerStepWorkReportID = String.Empty '报工ID
        '如果不存在工序报工，则要添加一条工序报工
        If dtWorkReport.Rows.Count = 0 Then
            '插入工序报工（自动报工）
            containerStepWorkReportID = Guid.NewGuid.ToString()
            sql = String.Format(<string>INSERT INTO containerstepworkreport(checktype,containerid,containerstepdispatchid,containerstepworkreportid,createdate,factoryid,isuse,qty,workerid,workflowstepid,lossqty)
                                                VALUES('30','{0}','{1}','{2}',to_date('{3}','yyyy-MM-dd hh24:mi:ss'),'{4}',0,{5},'{6}','{7}',{8})</string>.Value,
                                containerID, "", containerStepWorkReportID, DateTime.Now.ToString(), subFactoryid, passqty + lossqty, reportemployeeid, workflowstepID, lossqty)
            sqlList.Add(sql)
        Else
            containerStepWorkReportID = dtWorkReport.Rows(0)("containerstepworkreportid").ToString()
        End If
        '插入工序检验
        sql = String.Format(<string>INSERT INTO containercheckinfo(acceptanceqty,checkerid,checkqty,containercheckinfoid,containerid,createdate,defectqty,factoryid,isuse,lossqty,reworkqty,workflowstepid,containerstepworkreportid,acceptanceqty2)
                                            VALUES({0},'{1}',{2},'{3}','{4}',to_date('{5}','yyyy-MM-dd hh24:mi:ss'),{6},'{7}',0,{8},{9},'{10}','{11}',{12})</string>.Value,
                                passqty, checkemployeeid, passqty + lossqty, Guid.NewGuid.ToString(), containerID, DateTime.Now.ToString(), defectqty,
                                subFactoryid, lossqty, reworkqty, workflowstepID, containerStepWorkReportID, passqty)
        sqlList.Add(sql)
        DatabaseHelper.ExecuteSqlTran(sqlList)
        result.Result = True
        result.Message = "保存成功"
        Dim dic As New Dictionary(Of String, String)
        dic.Add("ReportDate", reportdate)
        dic.Add("CheckDate", checkdate)
        dic.Add("ContainerID", containerID)
        dic.Add("WorkflowstepID", workflowstepID)
        result.ReturnData = dic
        Return result
    End Function
#End Region

End Class
